package com.nuoniu.sibanyun.mapper.provider;

import com.nuoniu.sibanyun.common.lang.StringUtils;
import com.nuoniu.sibanyun.entity.SysCompany;
import com.nuoniu.sibanyun.entity.SysUser;
import com.nuoniu.sibanyun.entity.dto.SysUserDto;
import org.apache.ibatis.jdbc.SQL;
import org.apache.poi.ss.formula.functions.T;

/**
 * @auther 小啊刚
 * @date 2020/6/3
 */
public class SysUserMapperProvider {

    /**对应数据库表名**/
    private String TABLE_NAME = "sys_user u";

    /**
     * 获取用户列表
     * @author 小啊刚
     * @date 2020/6/3
     */
    public String getUserPage(SysUserDto userDto) {
        return new SQL() {
            {
                SELECT("u.id as 'key', u.id id, u.user_name userName, u.real_name realName," +
                        "u.`password` password, u.mobile mobile, u.email email, u.remarks remarks," +
                        "u.status status, e.office_id officeId, e.office_name officeName, ur.role_id roleId, sr.role_name roleName");
                FROM(TABLE_NAME);
                LEFT_OUTER_JOIN("sys_user e on u.id = e.id ");
                LEFT_OUTER_JOIN("sys_user_role ur on ur.user_id = u.id");
                LEFT_OUTER_JOIN("sys_role sr on sr.id = ur.role_id");
                WHERE("u.company_id = #{userDto.companyId}");
                if(StringUtils.isNotBlank(userDto.getRealName())) {
                    WHERE("u.real_name like \"%\"#{userDto.realName}\"%\"");
                }
                if(StringUtils.isNotBlank(userDto.getUserName())) {
                    WHERE("u.user_name like \"%\"#{userDto.userName}\"%\"");
                }
                if(userDto.getOfficeId() != null) {
                    WHERE("(e.office_id = #{userDto.officeId} or e.office_code like #{userDto.officeCode}\"%\")");
                }
                if(userDto.getStatus() != null) {
                    WHERE("u.status = #{userDto.status}");
                }
                ORDER_BY("field(roleId, 9) desc, roleName is null, roleName desc, u.real_name");
            }
        }.toString();
    }

    /**
     * 获取下级会员列表
     * @author 小啊刚
     * @date 2020-07-28
     */
    public String getShareUserList(SysUser user) {
        return new SQL() {
            {
                SELECT("u.*");
                FROM(TABLE_NAME);
                WHERE("u.share_user_id = #{user.shareUserId} or id=#{user.shareUserId}");
                if(StringUtils.isNotBlank(user.getRealName())) {
                    WHERE("u.real_name like \"%\"#{user.realName}\"%\"");
                }
                if(StringUtils.isNotBlank(user.getMobile())) {
                    WHERE("u.mobile like \"%\"#{user.mobile}\"%\"");
                }
            }
        }.toString();
    }

    /**
     * 获取下级公司会员
     * @author 小啊刚
     * @date 2020-07-29
     */
    public String getShareCrmList(SysCompany company, Integer index) {
        String sql = "SELECT " +
                "*  " +
                "FROM " +
                "( " +
                "SELECT " +
                "row_number() over ( PARTITION BY o.company_id ORDER BY o.pay_date DESC ) AS rn, " +
                "c.company_name companyName, " +
                "c.id companyId, " +
                "c.share_user_id, " +
                "o.pay_date payDate, " +
                "o.sale_price salePrice, " +
                "o.create_date createDate, " +
                "c.create_date registDate, " +
                "c.create_by createBy, " +
                "c.mobile, " +
                "c.login_date loginDate, " +
                "c.share_user_name shareUserName, " +
                "(select FIND_IN_SET(#{company.shareUserId}, share_user_ids) from sys_user u where FIND_IN_SET(#{company.shareUserId}, share_user_ids) and c.share_user_id = u.id) as grade " +
                "FROM " +
                "crm_goods_order o " +
                "RIGHT JOIN sys_tenant c ON o.company_id = c.id  " +
                "WHERE " +
                "c.share_user_id = #{company.shareUserId} or c.share_user_id in (select id from sys_user where FIND_IN_SET(#{company.shareUserId}, share_user_ids) <= #{index} AND FIND_IN_SET(#{company.shareUserId}, share_user_ids ) > 0) " +
                ") t  " +
                "WHERE " +
                "(t.rn = 1 or createDate is null) ";
        if(StringUtils.isNotBlank(company.getCompanyName())) {
            sql += " and companyName like \"%\"#{company.companyName}\"%\"";
        }
        if(company.getId() != null) {
            sql += " and companyId = #{company.id}";
        }
        return sql;
    }
}
